﻿Imports MySql.Data.MySqlClient
Imports System.IO
Imports System.Data

Public Class Editdevice


    Dim ipconnect As String

    Dim usernamedb As String
    Dim dbname As String
    Dim mysqlpass As String
    Dim id_user As String
    Dim position_user As String

    Public Sub New(ByRef mysql_pass As String, ByRef ip_connect As String, ByRef user_namedb As String, ByRef db_name As String)
        InitializeComponent()
        mysqlpass = mysql_pass


        ipconnect = ip_connect
        usernamedb = user_namedb
        dbname = db_name
    End Sub
    Dim sql As MySqlConnection
    Private Sub Editdevice_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sql = New MySqlConnection
        sql.ConnectionString = "server=" + ipconnect + ";user id=" + usernamedb + ";password=" + mysqlpass + ";database=" + dbname + ";Character Set =utf8;"
        Try
            sql.Open()
            ' MsgBox("CONNECTED TO DATABASE")
        Catch ex As Exception
            MsgBox("Can't Connect to database" + ex.Message)

            Me.Close()
        End Try

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim key As String
        Dim count As Integer

        count = 0
        key = txtsearch.Text

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If

        'mySqlCommand.CommandText = "SELECT DISTINCT * from Employee where dept_id in (SELECT dept_id from Department where dept_name like" + " '%" + TextBox1.Text + "%' )" + " or emp_name like " + "'%" + TextBox1.Text + "%'" + " or emp_surname like " + "'%" + TextBox1.Text + "%'" + " or emp_position like " + "'%" + TextBox1.Text + "%'" + " or emp_level like " + "'%" + TextBox1.Text + "%'" + ";"
        mySqlCommand.CommandText = "SELECT * FROM data_device join department on  data_device.idDepartment = department.idDepartment where  type like" + "'%" + key + "%' or  name like '%" + key + "%'  or  model like '%" + key + "%'   or  c_cpu like '%" + key + "%'  or  c_mainboard like '%" + key + "%' or  c_ram like '%" + key + "%' or  c_harddisk like '%" + key + "%' or  c_vgacard like '%" + key + "%'  or  state_device like '%" + key + "%' or  p_type like '%" + key + "%'or  m_size like '%" + key + "%' or  detail like '%" + key + "%'or  c_ipnumber like '%" + key + "%' or  c_ps like '%" + key + "%' or  c_cd like '%" + key + "%' or  c_case like '%" + key + "%' or  serialnumber like '%" + key + "%' or  c_comname like '%" + key + "%' or  c_windows like '%" + key + "%' or  c_office like '%" + key + "%' order by iddata_device;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand


        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListView1.Items.Clear()

            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("iddata_device"))
                    If mySqlReader("type") = "Computer" Then
                        .SubItems.Add("Computer")
                    End If
                    If mySqlReader("type") = "P" Then
                        .SubItems.Add("Printer")
                    End If
                    If mySqlReader("type") = "M" Then
                        .SubItems.Add("Monitor")
                    End If
                    If mySqlReader("type") = "O" Then
                        .SubItems.Add("Other")
                    End If
                    If mySqlReader("type") = "L" Then
                        .SubItems.add("Licens")
                    End If
                    If mySqlReader("type") = "N" Then
                        .SubItems.add("Network")
                    End If
                    .SubItems.Add(mySqlReader("name"))

                    If mySqlReader("type") = "Computer" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "CPU : " + mySqlReader("c_cpu") + "  mainboard : " + mySqlReader("c_mainboard") + "   RAM : " + mySqlReader("c_ram") + "   HARDDISK : " + mySqlReader("c_harddisk") + "   VGA Card : " + mySqlReader("c_vgacard") + "   Ip Number : " + mySqlReader("c_ipnumber") + "   Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "   PowerSupply : " + mySqlReader("c_ps") + "   CD/DVD : " + mySqlReader("c_cd") + "   Case : " + mySqlReader("c_case") + "   IpAddress : " + mySqlReader("c_ipnumber") + "   Computername : " + mySqlReader("c_comname") + "   Windows : " + mySqlReader("c_windows") + "   Office : " + mySqlReader("c_office") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "M" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "  Size : " + mySqlReader("m_size") + "   Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "P" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "  Type : " + mySqlReader("p_type") + "   Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "O" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "  Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "L" Then
                        .SubItems.Add("Detail : " + mySqlReader("detail") + "  Price : " + mySqlReader("price") + "Amount : " + mySqlReader("amount"))
                    End If
                    If mySqlReader("type") = "N" Then
                        .SubItems.Add("Serial Number : " + mySqlReader("serialnumber") + "IP Address : " + mySqlReader("c_ipnumber") + "Pass-Connect : " + mySqlReader("pass_connect") + "Pass-Config : " + mySqlReader("pass_config") + "Detail : " + mySqlReader("detail") + "Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("state_device") IsNot DBNull.Value Then
                        .SubItems.Add(mySqlReader("state_device"))
                    End If
                    If mySqlReader("departmentname") IsNot DBNull.Value Then
                        .SubItems.Add(mySqlReader("departmentname"))
                    End If
                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()



    End Sub

    Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
        showdata()
    End Sub


    Private Sub showdata()
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim key As String
        Dim count As Integer

        count = 0
        key = txtsearch.Text

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If

        mySqlCommand.CommandText = "SELECT * FROM data_device join department on  data_device.idDepartment = department.idDepartment where  type like" + "'%" + key + "%' or  name like '%" + key + "%'  or  model like '%" + key + "%'   or  c_cpu like '%" + key + "%'  or  c_mainboard like '%" + key + "%' or  c_ram like '%" + key + "%' or  c_harddisk like '%" + key + "%' or  c_vgacard like '%" + key + "%'  or  state_device like '%" + key + "%' or  p_type like '%" + key + "%'or  m_size like '%" + key + "%' or  detail like '%" + key + "%'or  c_ipnumber like '%" + key + "%' or  c_ps like '%" + key + "%' or  c_cd like '%" + key + "%' or  c_case like '%" + key + "%' or  serialnumber like '%" + key + "%' or  c_comname like '%" + key + "%' or  c_windows like '%" + key + "%' or  c_office like '%" + key + "%' order by iddata_device;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand


        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListView1.Items.Clear()

            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("iddata_device"))
                    If mySqlReader("type") = "Computer" Then
                        .SubItems.Add("Computer")
                    End If
                    If mySqlReader("type") = "P" Then
                        .SubItems.Add("Printer")
                    End If
                    If mySqlReader("type") = "M" Then
                        .SubItems.Add("Monitor")
                    End If
                    If mySqlReader("type") = "O" Then
                        .SubItems.Add("Other")
                    End If
                    If mySqlReader("type") = "L" Then
                        .SubItems.add("Licens")
                    End If
                    If mySqlReader("type") = "N" Then
                        .SubItems.add("Network")
                    End If
                    .SubItems.Add(mySqlReader("name"))

                    If mySqlReader("type") = "Computer" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "CPU : " + mySqlReader("c_cpu") + "  mainboard : " + mySqlReader("c_mainboard") + "   RAM : " + mySqlReader("c_ram") + "   HARDDISK : " + mySqlReader("c_harddisk") + "   VGA Card : " + mySqlReader("c_vgacard") + "   Ip Number : " + mySqlReader("c_ipnumber") + "   Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "   PowerSupply : " + mySqlReader("c_ps") + "   CD/DVD : " + mySqlReader("c_cd") + "   Case : " + mySqlReader("c_case") + "   IpAddress : " + mySqlReader("c_ipnumber") + "   Computername : " + mySqlReader("c_comname") + "   Windows : " + mySqlReader("c_windows") + "   Office : " + mySqlReader("c_office") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "M" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "  Size : " + mySqlReader("m_size") + "   Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "P" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "  Type : " + mySqlReader("p_type") + "   Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "O" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "  Detail : " + mySqlReader("detail") + "   Serial Number : " + mySqlReader("serialnumber") + "  Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "L" Then
                        .SubItems.Add("Detail : " + mySqlReader("detail") + "  Price : " + mySqlReader("price") + "Amount : " + mySqlReader("amount"))
                    End If
                    If mySqlReader("type") = "N" Then
                        .SubItems.Add("Serial Number : " + mySqlReader("serialnumber") + "IP Address : " + mySqlReader("c_ipnumber") + "Pass-Connect : " + mySqlReader("pass_connect") + "Pass-Config : " + mySqlReader("pass_config") + "Detail : " + mySqlReader("detail") + "Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("state_device") IsNot DBNull.Value Then
                        .SubItems.Add(mySqlReader("state_device"))
                    End If
                    If mySqlReader("departmentname") IsNot DBNull.Value Then
                        .SubItems.Add(mySqlReader("departmentname"))
                    End If
                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub

    Private Sub txtsearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtsearch.KeyDown
        If e.KeyCode = "13" Then
            showdata()
        End If
    End Sub

    Private Sub เพมขอมลอปกรณToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles เพมขอมลอปกรณToolStripMenuItem.Click
        Dim NextForm As add = New add(mysqlpass, ipconnect, usernamedb, dbname)
        '  Dim NextForm As main_user = New main_user()
        Me.Hide()
        NextForm.ShowDialog()
    End Sub

    Private Sub แกไขขอมลอปกรToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles แกไขขอมลอปกรToolStripMenuItem.Click
        Dim NextForm As Edit2 = New Edit2(mysqlpass, ipconnect, usernamedb, dbname)
        '  Dim NextForm As main_user = New main_user()
        NextForm.Show()
        Me.Hide()
    End Sub

    Private Sub บนทกประวตบำรงรกษาToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles บนทกประวตบำรงรกษาToolStripMenuItem.Click

    End Sub

    Private Sub เพมแผนกToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles เพมแผนกToolStripMenuItem.Click
        Dim NextForm As Adddepartment = New Adddepartment(mysqlpass, ipconnect, usernamedb, dbname)
        '  Dim NextForm As main_user = New main_user()
        NextForm.Show()
        Me.Hide()
    End Sub

    Private Sub จองอปกรณToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles จองอปกรณToolStripMenuItem.Click
        Dim NextForm As device_department = New device_department(mysqlpass, ipconnect, usernamedb, dbname)
        '  Dim NextForm As main_user = New main_user()
        NextForm.Show()
        Me.Hide()
    End Sub
End Class